//
//  DataBaseHandle.m
//  UI18_SQLite 数据库
//
//  Created by dllo on 15/8/5.
//  Copyright (c) 2015年 dllo. All rights reserved.
//

#import "DataBaseHandle.h"
#import <sqlite3.h>
#import "Enshrine.h"
@implementation DataBaseHandle

#pragma mark - 单例
/* 创建单例方法
 * 注意是类方法
 */
+ (instancetype)shareDataBaseHandle{
    /*
     * static 修饰词作用,  1.在静态区开空间, 程序退出时, 内存才释放
     *                  2.只初始化一次
     */
    static DataBaseHandle *databaseHandle = nil;
    
    if (databaseHandle == nil) {
        databaseHandle = [[DataBaseHandle alloc] init];
    }
    
    return databaseHandle;
    
    
}
#pragma mark - 数据库相关操作

/* 创建一个数据库对象 */
static sqlite3 *db;

/* 打开数据库 */
- (void)open{
    NSString *docPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    
    /* 数据库文件路径 */
    NSString *sqlPath = [docPath stringByAppendingPathComponent:@"collects.sqlite"];
    
    NSLog(@"%@", sqlPath);
    
    /* 如果数据库已经打开, 直接返回, 不运行下面代码 */
    if (db != nil) {
        NSLog(@"数据库已经打开");
        return;
    }
    
    /* 核心API: sqlite3_open 函数 */
    int result = sqlite3_open(sqlPath.UTF8String, &db);
    
    /* 根据函数返回值, 判断操作是否正确 */
    if (result == SQLITE_OK) {
        NSLog(@"数据库打开成功");
    }else{
        NSLog(@"数据库打开失败");
    }
   
}

/* 创建表单 */
- (void)createTable{
    
    /* 创建SQL语句 */
    NSString *createSQL = @"CREATE TABLE IF NOT EXISTS myCollects(number integer PRIMARY KEY AUTOINCREMENT, picture TEXT, title TEXT, address TEXT, url TEXT)";
    
    /* 核心API: sqlite3_exec */
    int result = sqlite3_exec(db, createSQL.UTF8String, NULL, NULL, nil);
    
    if (result == SQLITE_OK) {
        NSLog(@"创建表格成功");
    }else{
        NSLog(@"创建表格失败");
    }
    
    
}

/* 插入数据(收藏信息) */
- (void)insertCollect:(Enshrine *)model{
    /* 创建SQL语句 */
    NSString *insertSQL = [NSString stringWithFormat:@"INSERT INTO myCollects(picture,title,address,url) VALUES ('%@', '%@', '%@', '%@')", model.picture, model.title, model.address, model.url];
    
    /* 核心API: sqlite3_exec 函数 */
    int result = sqlite3_exec(db, insertSQL.UTF8String, NULL, NULL, nil);
    
    if (SQLITE_OK == result) {
        NSLog(@"插入数据成功");
    }else{
        NSLog(@"插入数据失败");
    }
}

/* 删除数据 */
- (void)deleteCollect:(NSString *)title{
    NSString *deleteSQL = [NSString stringWithFormat:@"DELETE FROM myCollects WHERE title = '%@'", title];//这里可能会有问题
    /* 核心API: */
    int result = sqlite3_exec(db, deleteSQL.UTF8String, NULL, NULL, nil);
    if (SQLITE_OK == result) {
        NSLog(@"删除成功");
    }else{
        NSLog(@"删除失败");
    }
}

/* 关闭数据库 */
- (void)close{
    int result = sqlite3_close(db);
    if (SQLITE_OK == result) {
        /* 将指针指向空指针 */
        db = nil;
        NSLog(@"关闭数据库成功");
    }else{
        NSLog(@"关闭数据库失败");
    }
}

/* 查询收藏信息 */
- (NSArray *)selectCollectWithcontentURL:(NSString *)title{
    /* 创建数组, 存放符合查询条件的对象 */
    NSMutableArray *array = [NSMutableArray array];
    
    /* 创建SQL查询语句 */
    NSString *selectSQL = [NSString stringWithFormat:@"SELECT * FROM myCollects WHERE title = '%@'", title];
    
    /* 核心API: */
    /* 准备好的SQL语句对象 */
    sqlite3_stmt *stmt = nil;
    
    /* 编译SQL语句 */
    int result = sqlite3_prepare_v2(db, selectSQL.UTF8String, -1, &stmt, nil);
    
    if (SQLITE_OK == result) {
        NSLog(@"查询成功");
        
        /* sqlite3_step(stmt); 执行SQL语句
         * 执行SQL语句
         * 此函数的返回值为 SQLITE_ROW时, 说明还有row(行)可以
         */

        //循环 判断到什么时候结束
        while (SQLITE_ROW == sqlite3_step(stmt)) {
            
            //但需要对象查询之后的结果进行处理操作时，使用sqlite3_column_ 函数
            const unsigned char *picture = sqlite3_column_text(stmt, 1);//取出picture
            const unsigned char *title = sqlite3_column_text(stmt, 2);//取出title
            const unsigned char *address = sqlite3_column_text(stmt, 3);//取出address
            const unsigned char *url = sqlite3_column_text(stmt, 4); //取出url
            
            /* 创建Model对象, 赋值 */
            Enshrine *model = [[Enshrine alloc] init];
            model.picture = [NSString stringWithUTF8String:(const char *)picture];
            model.title = [NSString stringWithUTF8String:(const char *)title];
            model.address = [NSString stringWithUTF8String:(const char *)address];
            model.url = [NSString stringWithUTF8String:(const char *)url];
            
            /* model对象添加到数组中 */
            [array addObject:model];
            
        }
        
        
        /* 销毁 准备好的SQL语句对象 */
        sqlite3_finalize(stmt);
    }else{
        
        NSLog(@"查询失败");
    }
    return array;
}

/* 查询全部收藏信息 */
- (NSArray *)selectAllCollects{
    /* 创建数组, 存放符合查询条件的对象 */
    NSMutableArray *array = [NSMutableArray array];
    
    /* 创建SQL查询语句 */
    NSString *selectSQL = [NSString stringWithFormat:@"SELECT * FROM myCollects"];
    
    /* 核心API: */
    /* 准备好的SQL语句对象 */
    sqlite3_stmt *stmt = nil;
    
    /* 编译SQL语句 */
    int result = sqlite3_prepare_v2(db, selectSQL.UTF8String, -1, &stmt, nil);
    
    if (SQLITE_OK == result) {
        NSLog(@"查询成功");
        
        /* sqlite3_step(stmt); 执行SQL语句
         * 执行SQL语句
         * 此函数的返回值为 SQLITE_ROW时, 说明还有row(行)可以
         */
        
        //循环 判断到什么时候结束
        while (SQLITE_ROW == sqlite3_step(stmt)) {
            
            //但需要对象查询之后的结果进行处理操作时，使用sqlite3_column_ 函数
            const unsigned char *picture = sqlite3_column_text(stmt, 1);//取出picture
            const unsigned char *title = sqlite3_column_text(stmt, 2);//取出title
            const unsigned char *address = sqlite3_column_text(stmt, 3);//取出address
            const unsigned char *url = sqlite3_column_text(stmt, 4); //取出url
            
            /* 创建Model对象, 赋值 */
            Enshrine *model = [[Enshrine alloc] init];
            model.picture = [NSString stringWithUTF8String:(const char *)picture];
            model.title = [NSString stringWithUTF8String:(const char *)title];
            model.address = [NSString stringWithUTF8String:(const char *)address];
            model.url = [NSString stringWithUTF8String:(const char *)url];
            
            /* model对象添加到数组中 */
            [array addObject:model];
        }
        
        
        /* 销毁 准备好的SQL语句对象 */
        sqlite3_finalize(stmt);
    }else{
        
        NSLog(@"查询失败");
    }
    return array;
}



@end
